import pandas as pd
from work_zykj.mysql import mysql_util
from work_zykj.oracle.oracle_util import oracle_util_class

import json
import math

"""查询手机号创建的场"""

if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/test3.xlsx'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path, sheet_name='Sheet1')

    # 打印读取到的数据
    # print(df)
    df2 = pd.DataFrame()
    #nmdc的连接
    for index, row in df.iterrows():
        mobile = row['手机号']
        mobile = "%.0f" % mobile
        print(type(mobile))
        print(mobile)
        sql = "select t.org_code, t.farm_org, t.name_loc farm_name, to_char(t.create_date,'yyyy/MM/dd') as 创建日期, t.user_create " \
              " ,'专业版' 类型 from fr_farm_org t where nvl(t.cancel_flag, 'N') = 'N' and t.management_flg = 'M' and t.org_code not like 'X%' " \
              "and t.create_date > to_date('2024/01/01', 'yyyy/MM/dd') and t.user_create like '%{}%' " \
              "order by t.org_code, t.farm_org ".format(mobile)
        oracle_util = oracle_util_class()
        rs = oracle_util.select(sql, 'NMDCFARM')
        rs = json.loads(rs)
        # print(rs)
        # df2 = df2.append(rs)
        for d in rs:
            d['手机号'] = mobile
            list_ = [d]
            df2 = pd.concat([df2, pd.DataFrame(list_)], ignore_index=True)
        # 查询家庭版
        sql = "SELECT distinct a.farm_id org_code, a.farm_id as farm_org,a.farm_name,a.user_create,a.create_date,DATE_FORMAT(a.create_date, '%Y-%m-%d') 创建日期 " \
              " ,'家庭版' 类型 FROM bae_farm_information a inner join pc_user u on u.external_id = a.user_create " \
              " and a.create_date >= '2024-01-01 00:00:00' and u.mobile = '{}' " \
              " order by a.id".format(mobile)
        rs = mysql_util.select_by_sql(sql)
        rs = json.loads(rs)
        for d in rs:
            d['手机号'] = mobile
            list_ = [d]
            df2 = pd.concat([df2, pd.DataFrame(list_)], ignore_index=True)

    df2.to_excel('C:/Users/yangjianzhang/Desktop/test2.xlsx', index=False)
